const express = require('express');
const router = express.Router()
const pool = require('../utils/pool.js')
const $ = require('../utils/method')
const resp = require('../utils/res')
//服务业务分类
router.get('/serve-type',(req,res,next)=>{
  const sql = 'select * from classify,sclassification where clid = cid'
  pool.query(sql,(err,r)=>{
    if(err){
      return next()
    }
    res.send({code:200, msg:'ok',data:$.CombineData(r,['clid','busclass','avatar'])})
  })
})

//按业务类型分类查询所有服务

router.get('/serve-queryAll',(req,res,next)=>{
  const sql ='SELECT clid,busclass,scid,vwork FROM sclassification,classify where cid=clid;select clid,sdid,title,show_price,unit,simg,sdecr FROM sclassification join classify on cid=clid join sdetails on vwork=swork '
  pool.query(sql,(err,r)=>{
    if(err){
      return next(err)
    }
    let dat = $.CombineData(r[0],['clid','busclass'])
    let sdet = $.CombineData(r[1],['clid'])
    let data = dat.map(v=>{
       sdet.forEach(val=>{
        if(v.clid==val.clid){
          v.sdlist= val.redetalis
        }
      })
      return v
    })
    res.send(resp.ok(data))
  })
})



//按服务类型及地区分类
router.get('/serve-ByType',(req,res,next)=>{
  let page = req.query.page-1 || 1
  //按服务类型分类查询服务详情sql语句
  const sql = 'select SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit from sdetails,shop where shid = sid and city = ? and swork =? limit ?,?;SELECT FOUND_ROWS() total'

  //查询业务分类下的全部服务sql语句
  const sql1 = 'SELECT SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit FROM sdetails,shop where swork in (SELECT vwork FROM classify,sclassification WHERE cid = clid AND busclass =?) and shid = sid and city = ? limit ?,?;SELECT FOUND_ROWS() total'
  const {busclass,swork} = req.query
  if(swork==='全部'){
    pool.query(sql1,[busclass,req.city,req.start,req.pages],(err,r)=>{
      if(err){
        return next(err)
      }
      res.send({
        code:200,
        msg:'ok',
        data:r[0],
        page,
        pagesize:req.pages,
        total:r[1][0].total
      })
    })
  }else{
    pool.query(sql,[req.city,swork,req.start,req.pages],(err,r)=>{
      if(err){
        return next(err)
      }
      res.send({
        code:200,
        msg:'ok',
        data:r[0],
        page,
        pagesize:req.pages,
        total:r[1][0].total
      })
    })
  }
})

//按服务详情id查询服务
router.get('/serve-ById',(req,res,next)=>{
  const sql = 'select shop,sdid,title,show_price,unit,count,simg,specifications,sdecr,srcdesc,stime,salestotal,estimate,time_limit from sdetails,shop where shid=sid and sdid = ?'
  pool.query(sql,req.query.id,(err,r)=>{
    if(err){
      return next()
    }
    res.send({code:200,msg:'ok',data:r})
  })
})

//按销量排序
router.get('/serve-sort-sales',(req,res,next)=>{
  const {busclass,swork,page} = req.query
    //按服务类型分类查询服务详情sql语句
    const sql = `select SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit from sdetails,shop where shid = sid and city = ? and swork =? order by salestotal desc limit ?,?;SELECT FOUND_ROWS() total`
  
    //查询业务分类下的全部服务sql语句
    const sql1 = `SELECT SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit FROM sdetails,shop where swork in (SELECT vwork FROM classify,sclassification WHERE cid = clid AND busclass =?) and shid = sid and city = ? order by salestotal desc limit ? , ?;SELECT FOUND_ROWS() total`
    if(swork === '全部'){
      pool.query(sql1,[busclass,req.city,req.start,req.pages],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send({
          code:200,
          msg:'ok',
          data:r[0],
          page:page-0||1,
          pagesize:req.pages,
          total:r[1][0].total
        })
      })
    }else{
      pool.query(sql,[req.city,swork,req.start,req.pages],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send({
          code:200,
          msg:'ok',
          data:r[0],
          page:page-0||1,
          pagesize:req.pages,
          total:r[1][0].total
        })
      })
    }
})

//按好评排序
router.get('/serve-sort-estim',(req,res,next)=>{
  const {busclass,swork,page} = req.query
    //按服务类型分类查询服务详情sql语句
    const sql = `select SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit from sdetails,shop where shid = sid and city = ? and swork =? order by estimate desc limit ?,?;SELECT FOUND_ROWS() total`
  
    //查询业务分类下的全部服务sql语句
    const sql1 = `SELECT SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit FROM sdetails,shop where swork in (SELECT vwork FROM classify,sclassification WHERE cid = clid AND busclass =?) and shid = sid and city = ? order by estimate desc limit ?,?;SELECT FOUND_ROWS() total`
    if(swork==='全部'){
      pool.query(sql1,[busclass,req.city,req.start,req.pages],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send({
          code:200,
          msg:'ok',
          data:r[0],
          page:page-0||1,
          pagesize:req.pages,
          total:r[1][0].total
        })
      })
    }else{
      pool.query(sql,[req.city,swork,req.start,req.pages],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send({
          code:200,
          msg:'ok',
          data:r[0],
          page:page-0||1,
          pagesize:req.pages,
          total:r[1][0].total
        })
      })
    }
})

//上门速度排序
router.get('/serve-sort-sotime',(req,res,next)=>{
  const {busclass,swork,page} = req.query
    //按服务类型分类查询服务详情sql语句
    const sql = `select SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit from sdetails,shop where shid = sid and city = ? and swork =? order by time_limit asc limit ? , ?;SELECT FOUND_ROWS() total`
  
    //查询业务分类下的全部服务sql语句
    const sql1 = `SELECT SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit FROM sdetails,shop where swork in (SELECT vwork FROM classify,sclassification WHERE cid = clid AND busclass =?) and shid = sid and city = ? order by time_limit asc limit ? , ?;SELECT FOUND_ROWS() total`
    if(swork==='全部'){
      pool.query(sql1,[busclass,req.city,req.start,req.pages],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send({
          code:200,
          msg:'ok',
          data:r[0],
          page:page-0||1,
          pagesize:req.pages,
          total:r[1][0].total
        })
      })
    }else{
      pool.query(sql,[req.city,swork,req.start,req.pages],(err,r)=>{
        if(err){
          return next(err)
        }
        res.send({
          code:200,
          msg:'ok',
          data:r[0],
          page:page-0||1,
          pagesize:req.pages,
          total:r[1][0].total
        })
      })
    }
})

//按价格排序
router.get('/serve-sort-price',(req,res,next)=>{
  const {busclass,swork,page} = req.query
    //按服务类型分类查询服务详情sql语句
    const sql = `select sql_calc_found_rows sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit from sdetails,shop where shid = sid and city = ? and swork =? order by show_price asc limit ? , ?;SELECT FOUND_ROWS() total`
  
    //查询业务分类下的全部服务sql语句
    const sql1 = `SELECT sql_calc_found_rows sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit FROM sdetails,shop where swork in (SELECT vwork FROM classify,sclassification WHERE cid = clid AND busclass =?) and shid = sid and city = ? order by show_price asc limit ? , ?;SELECT FOUND_ROWS() total`
    if(swork==='全部'){
      pool.querySync(sql1,[busclass,req.city,req.start,req.pages])
      .then((result) => {
        res.send({
          code:200,
          msg:'ok',
          data:result[0],
          total:result[1][0].total,
          page:page-0||1,
          pagesize:req.pages
        })
      }).catch((err) => {
        next(err)
      });
    }else{
      pool.querySync(sql,[req.city,req.swork,req.start,req.pages])
      .then((result) => {
        res.send({
          code:200,
          msg:'ok',
          data:result[0],
          total:result[1][0].total,
          page:page-0||1,
          pagesize:req.pages
        })
      }).catch((err) => {
        next(err)
      });
    }
})


//按服务商id查询服务详情列表
router.get('/serve-ByMnameID',(req,res,next)=>{
  const {id,page} = req.query
  const sql = 'select sql_calc_found_rows sdid,shop_ident,title,show_price,unit,count,simg,sdecr,salestotal,estimate from sdetails,shop where shid = sid and mname=(select me_name from merchant_info where meid = ?) LIMIT ?,?;SELECT FOUND_ROWS() total;select merchant_info.*,sum(salestotal) salesSum,sum(estimate) good_repu from shop JOIN sdetails ON sid=shid JOIN merchant_info ON mname=me_name WHERE meid=?'
  pool.querySync(sql,[id,req.start,req.pages,id])
  .then((result) => {
    const merchant = result[2]
    res.send({
      code:200,
      msg:'ok',
      data:{sdlist:result[0],...merchant},
      total:result[1][0].total,
      page:page-0||1,
      pagesize:req.pages
    })
  })
})

//查询全部服务详情
router.get('/server-list',(req,res,next)=>{
  let page = req.query.page-0 || 1
  const sql = 'select shop.mname,shop.shop_ident,sdetails.* from sdetails,shop WHERE shid=sid LIMIT ?,?;select count(*) count from sdetails,shop where shid=sid'
  pool.query(sql,[req.start,req.pages],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send({
      code:200,
      msg:'ok',
      data:r[0],
      total:r[1][0].count,
      page:page-0||1,
      pagesize:req.pages})
  })
})

//关键字模糊搜索
router.get('/search-Bykey',(req,res,next)=>{
  let {key,page} = req.query
  key = '%'+key+'%'
  const sql ='select SQL_CALC_FOUND_ROWS sdid,mname,title,show_price,unit,count,simg,sdecr,salestotal,estimate,time_limit from sdetails join shop on shid=sid join sclassification on swork=vwork JOIN classify on cid=clid WHERE mname LIKE ? or title LIKE ? or busclass LIKE ? or swork LIKE ? limit ?,?;SELECT FOUND_ROWS() total'

  pool.query(sql,[key,key,key,key,req.start,req.pages],(err,r)=>{
    if(err){
      return next(err)
    }
    res.send({
      code:200,
      msg:'ok',
      data:r[0],
      total:r[1][0].total,
      page:page-0||1,
      pagesize:req.pages})
  })
})

module.exports = router